package cn.clwxy.gdz.dao;

import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.clwxy.gdz.bo.TeacherInfoBean;
import cn.clwxy.gdz.entity.ImgId;
import cn.clwxy.gdz.entity.Teacher;
import cn.clwxy.gdz.util.BaseDao;

public class TeacherDaoImpl extends BaseDao implements TeacherDao {

	@Override
	public List<Teacher> findTeacher() {
		String sql = "SELECT teacherId id,teacherPwd,teacherPhone phone FROM teacher";
		return super
				.queryList(sql, new BeanListHandler<Teacher>(Teacher.class));
	}

	@Override
	public List<Teacher> findTeacherByEducation(String education) {
		String sql = "SELECT * FROM teacher WHERE teacherEducation=?";
		return super.queryList(sql,
				new BeanListHandler<Teacher>(Teacher.class), education);
	}

	@Override
	public List<Teacher> findTeacherByAddress(String address) {
		String sql = "SELECT b.teacherUserName,b.teacherPwd,b.teacherRealName,"
				+ "b.teacherSex,b.teacherPhone,b.teacherType,b.teacherEducation,"
				+ "b.teacherUniversity,b.teacherSpeciality,b.teacherIdentity,b.teacherLevel,"
				+ "b.teacherDate,b.`teacherId` FROM  teacheraddress a INNER JOIN teacher b ON"
				+ "  a.userId=b.teacherId where addressDesc=?";
		return super.queryList(sql,
				new BeanListHandler<Teacher>(Teacher.class), address);
	}

	@Override
	public List<Teacher> findTeacherBySex(String sex) {
		String sql = "SELECT * FROM teacher WHERE teacherSex=?";
		return super.queryList(sql,
				new BeanListHandler<Teacher>(Teacher.class), sex);
	}

	@Override
	public List<Teacher> findTeacherByLevel(Integer level) {
		String sql = "SELECT * FROM teacher WHERE teacherLevel=?";
		return super.queryList(sql,
				new BeanListHandler<Teacher>(Teacher.class), level);
	}

	@Override
	/**
	 *  ��ʦҳ��..@author Administrator ����ǰ 
	 *   ���ѡ���������ѯ��ʦ��
	 */
	public List<Teacher> querTeacherList(String subject, String grade,
			String area, String sex, Integer startYear, Integer endYear,
			String education, String type, String workType, Integer pageNo) {

		String sql = "select t.teacherId as id,t.teacherRealName as realName,t.teacherSex as sex,imgId.src as img, \n"
				+ "t.teacherEducation as education, \n"
				+ "t.teachYear as teacherYear,ty.type as type, \n"
				+ "t.workType as workType,t.selfmark as selfMark,tempsubj.sub as subject, \n"
				+ "tempgrad.grd as grade \n"
				+ "from  teacher as t \n"
				+ "INNER JOIN(\n"
				+ "select  ts.teacherId ,GROUP_CONCAT(s.subjectName) as sub \n"
				+ "from teachersubjectrelative as ts \n"
				+ "INNER JOIN `subject` as s  on ts.subjectId=s.subjectId \n"
				+ "GROUP BY ts.teacherId \n"
				+ ")as  tempsubj on tempsubj.teacherId=t.teacherId \n"
				+ "INNER JOIN( \n"
				+ "select  tg.teacherId,GROUP_CONCAT(g.gradeName) as grd \n"
				+ "from teachergraderelative as tg \n"
				+ "INNER JOIN grade as g \n"
				+ "on tg.gradeId=g.id \n"
				+ "GROUP BY tg.teacherId \n"
				+ ")as tempgrad  on tempgrad.teacherId=t.teacherId \n"
				+ "INNER JOIN teacheraddress as td on t.teacherId=td.teacherId \n"
				+ "INNER JOIN addresscounty as adc on td.addressCountryId= adc.addressId \n"
				+ "inner join teachertype as ty on t.teacherType=ty.id \n"
				+ "inner join imgid on t.teacherImgId=imgid.id \n"
				+ "where \n"
				+ " tempsubj.sub like concat('%',?,'%') \n"
				+ "and tempgrad.grd like concat('%',?,'%') \n"
				+ " and adc.addressName like concat('%',?,'%') \n"
				+ "and t.teacherSex like CONCAT('%',?,'%') \n"
				+ "and t.teachYear in (select teachYear from teacher where teachYear BETWEEN ? and ? ) \n"
				+ "and t.teacherEducation like CONCAT('%',?,'%') \n"
				+ "and ty.type like concat('%',?,'%') \n"
				+ "and t.workType like concat('%',?,'%') \n"
				+ "ORDER BY t.teacherUpdateDate limit ?,12 ";

		// String
		// sql="select t.teacherId as teacherId,t.teacherRealName as teacherRealName,t.teacherEducation teacherEducation,t.teachYear as teachYear,imgid.src as teacherImgId,t.selfmark as selfmark from teacher as t "
		// +"INNER JOIN teachergraderelative as tg on t.teacherId=tg.teacherId INNER JOIN grade on tg.gradeId=grade.id INNER JOIN "
		// +
		// "teachersubjectrelative as ts on t.teacherId=ts.teacherId INNER JOIN `subject` as s on ts.subjectId=s.subjectId "
		// + "INNER JOIN teacheraddress as ta on t.teacherId=ta.teacherId "
		// +
		// "INNER JOIN addresscounty as ad on ta.addressCountryId=ad.addressId INNER JOIN teachertype as ty on t.teacherType=ty.id "
		// +
		// "INNER JOIN imgid on t.teacherImgId=imgid.id where s.subjectName like concat('%',?,'%') "
		// +
		// "and  gradeName like concat('%',?,'%') and ad.addressName like concat('%',?,'%') and "
		// +
		// "t.teacherSex like concat('%',?,'%') and t.teachYear like concat('%',?,'%') and t.teacherEducation like concat('%',?,'%') "
		// + "and ty.type like concat('%',?,'%') limit ?,12";
		Object[] obj = { subject, grade, area, sex, startYear, endYear,
				education, type, workType, (pageNo - 1) * 12 };
		return super.queryList(sql,
				new BeanListHandler<Teacher>(Teacher.class), obj);
	}

	@Override
	/**
	 * @author Administrator cxq ��ѯ����
	 */
	public Long queryCount(String subject, String grade, String area,
			String sex, Integer startYear, Integer endYear, String education,
			String type, String workType) {

		String sql = "select count(t.teacherId)\n "
				+ "from  teacher as t \n"
				+ "LEFT JOIN(\n"
				+ "select  ts.teacherId ,GROUP_CONCAT(s.subjectName) as sub \n"
				+ "from teachersubjectrelative as ts \n"
				+ "LEFT JOIN `subject` as s  on ts.subjectId=s.subjectId \n"
				+ "GROUP BY ts.teacherId \n"
				+ ")as  tempsubj on tempsubj.teacherId=t.teacherId \n"
				+ "LEFT JOIN( \n"
				+ "select  tg.teacherId,GROUP_CONCAT(g.gradeName) as grd \n"
				+ "from teachergraderelative as tg \n"
				+ "LEFT JOIN grade as g \n"
				+ "on tg.gradeId=g.id \n"
				+ "GROUP BY tg.teacherId \n"
				+ ")as tempgrad  on tempgrad.teacherId=t.teacherId \n"
				+ "LEFT JOIN teacheraddress as td on t.teacherId=td.teacherId \n"
				+ "LEFT JOIN addresscounty as adc on td.addressCountryId= adc.addressId \n"
				+ "LEFT join teachertype as ty on t.teacherType=ty.id \n"
				+ "LEFT join imgid on t.teacherImgId=imgid.id \n"
				+ "where \n"
				+ "tempsubj.sub like concat('%',?,'%') \n"
				+ "and tempgrad.grd like concat('%',?,'%') \n"
				+ " and adc.addressName like concat('%',?,'%') \n"
				+ "and t.teacherSex like CONCAT('%',?,'%') \n"
				+ "and t.teachYear in (select teachYear from teacher where teachYear BETWEEN ? and ? ) \n"
				+ "and t.teacherEducation like CONCAT('%',?,'%') \n"
				+ "and ty.type like concat('%',?,'%') \n"
				+ "and t.workType like concat('%',?,'%') \n"
				+ "ORDER BY t.teacherUpdateDate  ";

		// String sql="select count(t.teacherId) from teacher as t "
		// +"INNER JOIN teachergraderelative as tg on t.teacherId=tg.teacherId INNER JOIN grade on tg.gradeId=grade.id INNER JOIN "
		// +
		// "teachersubjectrelative as ts on t.teacherId=ts.teacherId INNER JOIN `subject` as s on ts.subjectId=s.subjectId "
		// + "INNER JOIN teacheraddress as ta on t.teacherId=ta.teacherId "
		// +
		// "INNER JOIN addresscounty as ad on ta.addressCountryId=ad.addressId INNER JOIN teachertype as ty on t.teacherType=ty.id "
		// +
		// "INNER JOIN imgid on t.teacherImgId=imgid.id where s.subjectName like concat('%',?,'%') "
		// +
		// "and  gradeName like concat('%',?,'%') and ad.addressName like concat('%',?,'%') and "
		// +
		// "t.teacherSex like concat('%',?,'%') and t.teachYear like concat('%',?,'%') and t.teacherEducation like concat('%',?,'%') "
		// + "and ty.type like concat('%',?,'%')";
		Object[] obj = { subject, grade, area, sex, startYear, endYear,
				education, type, workType };
		return super.queryColumn(sql, new ScalarHandler(), obj);
	}

	@Override
	public List<Teacher> queryTeacherList(Integer pageNo) {

		String sql = "select t.teacherId as id,t.teacherRealName as realName,t.teacherSex as sex,imgId.src as img, \n"
				+ "				t.teacherEducation as education,t.teacherPhone as phone,\n"
				+ "				t.teachYear as teacherYear,ty.type as type, \n"
				+ "				t.workType as workType,t.selfmark as selfMark,tempsubj.sub as subject, \n"
				+ "				tempgrad.grd as grade , t.teacherUpdateDate as updateTime,t.teacherLevel as level \n"
				+ "				from  teacher as t \n"
				+ "				LEFT JOIN(\n"
				+ "				select  ts.teacherId ,GROUP_CONCAT(s.subjectName) as sub \n"
				+ "				from teachersubjectrelative as ts \n"
				+ "				LEFT JOIN `subject` as s  on ts.subjectId=s.subjectId \n"
				+ "				GROUP BY ts.teacherId \n"
				+ "				)as  tempsubj on tempsubj.teacherId=t.teacherId \n"
				+ "				LEFT JOIN( \n"
				+ "				select  tg.teacherId,GROUP_CONCAT(g.gradeName) as grd \n"
				+ "				from teachergraderelative as tg \n"
				+ "				LEFT JOIN grade as g \n"
				+ "				on tg.gradeId=g.id \n"
				+ "				GROUP BY tg.teacherId \n"
				+ "				)as tempgrad  on tempgrad.teacherId=t.teacherId \n"
				+ "				LEFT JOIN teacheraddress as td on t.teacherId=td.teacherId \n"
				+ "				LEFT JOIN addresscounty as adc on td.addressCountryId= adc.addressId \n"
				+ "				LEFT join teachertype as ty on t.teacherType=ty.id \n"
				+ "				LEFT join imgid on t.teacherImgId=imgid.id where t.isDelete=0 order by t.teacherId desc  limit ?,15;";
		return super.queryList(sql,
				new BeanListHandler<Teacher>(Teacher.class), (pageNo - 1) * 15);
	}

	@Override
	public Long queryCount() {
		// TODO Auto-generated method stub
		String sql = "select count(t.teacherId)\n "
				+ "from  teacher as t \n"
				+ "LEFT JOIN(\n"
				+ "select  ts.teacherId ,GROUP_CONCAT(s.subjectName) as sub \n"
				+ "from teachersubjectrelative as ts \n"
				+ "LEFT JOIN `subject` as s  on ts.subjectId=s.subjectId \n"
				+ "GROUP BY ts.teacherId \n"
				+ ")as  tempsubj on tempsubj.teacherId=t.teacherId \n"
				+ "LEFT JOIN( \n"
				+ "select  tg.teacherId,GROUP_CONCAT(g.gradeName) as grd \n"
				+ "from teachergraderelative as tg \n"
				+ "LEFT JOIN grade as g \n"
				+ "on tg.gradeId=g.id \n"
				+ "GROUP BY tg.teacherId \n"
				+ ")as tempgrad  on tempgrad.teacherId=t.teacherId \n"
				+ "LEFT JOIN teacheraddress as td on t.teacherId=td.teacherId \n"
				+ "LEFT JOIN addresscounty as adc on td.addressCountryId= adc.addressId \n"
				+ "LEFT join teachertype as ty on t.teacherType=ty.id \n"
				+ "LEFT join imgid on t.teacherImgId=imgid.id where t.isDelete=0 ";
		return super.queryColumn(sql, new ScalarHandler());
	}

	@Override
	public int deletTeacher(Integer id) {
		String sql = "delete  teacher,teacheraddress,teachergraderelative,teachersubjectrelative,\n"
				+ "teachertype,imgid,`order` \n"
				+ " FROM teacher INNER JOIN teacheraddress on teacher.teacherId =teacheraddress.teacherId\n"
				+ "left JOIN teachergraderelative on teacher.teacherId=teachergraderelative.teacherId\n"
				+ "LEFT JOIN  teachersubjectrelative on teacher.teacherId=teachersubjectrelative.teacherId\n"
				+ "left JOIN teachertype on teacher.teacherId=teacherType.id\n"
				+ "left JOIN imgid on teacher.teacherId=imgid.id\n"
				+ "left JOIN `order` on teacher.teacherId=`order`.teacherId\n"
				+ "where teacher.teacherId=?";// �޸�״̬
		return super.update(sql, id);
	}

	@Override
	public int changeLevel(int id, int level) {
		String sql = "update teacher set teacherLevel=? where teacherId=? ";
		return super.update(sql, level, id);
	}

	@Override
	public int changePwd(Teacher teacher) {
		String sql = "UPDATE teacher SET teacherPwd=? where teacherId=?";
		return super.update(sql, teacher.getTeacherPwd(), teacher.getId());
	}

	@Override
	public String findTeacherById(Integer id) {
		String sql = "SELECT teacherPhone id from teacher WHERE teacherId=?";
		Teacher teacher = super.querySingle(sql, new BeanHandler<Teacher>(
				Teacher.class), id);
		return teacher.getPhone();
	}

	public static void main(String[] args) {
		int i = new TeacherDaoImpl().deletTeacher(9);
		System.out.println(i);
	}

	// 根据教师id改变imgId的值
	@Override
	public int changeHeadPic(Integer id, Integer imgId) {
		String sql = "UPDATE teacher SET teacherImgId=? WHERE teacherId=?";
		return super.update(sql, imgId, id);
	}

	// 错误方法，不能使用
	@Override
	public Integer findImgIdByTeacherId(Integer teacherId) {
		String sql = "SELECT teacherImgId FROM teacher WHERE teacherId=?";
		return super.querySingle(sql, new BeanHandler<Teacher>(Teacher.class),
				teacherId).getId();
	}

	@Override
	public int changeHeadPic(Integer id, String src) {
		// 在img表中新增一个图片
		String sql1 = "INSERT INTO imgid(src)value(?)";
		super.update(sql1, src);
		String sql2 = "SELECT id FROM imgid WHERE src=?";
		ImgId id2 = super.querySingle(sql2,
				new BeanHandler<ImgId>(ImgId.class), src);
		String sql3 = "UPDATE teacher SET teacherImgId=? WHERE teacherId=?";
		return super.update(sql3, id2.getId(), id);
	}

	@Override
	public int changeSex(Integer id, String sex) {
		String sql = "UPDATE teacher SET teacherSex=? where teacherId=?";
		return super.update(sql, sex, id);
	}

	@Override
	public int changeWorkType(Integer id, String workType) {
		String sql="UPDATE teacher SET workType=? where teacherId=?";
		return super.update(sql, workType, id);
	}

	@Override
	public int changeTeacherType(Integer id, Integer teacherType) {
		String sql="UPDATE teacher SET teacherType=? where teacherId=?";
		return super.update(sql, teacherType, id);
	}

	@Override
	public int changeEducation(Integer id, String education) {
		String sql="UPDATE teacher SET teacherEducation=? where teacherId=?";
		return super.update(sql, education, id);
	}

	@Override
	public int changeWorkYeas(Integer id, String workYears) {
		String sql="UPDATE teacher SET teachYear=? where teacherId=?";
		return super.update(sql, workYears, id);
	}

	@Override
	public TeacherInfoBean findTeacherGradesById(Integer teacherId) {
		String sql="SELECT GROUP_CONCAT(g.gradeName) gradeName from teacher t INNER JOIN teachergraderelative tg ON tg.teacherId=t.teacherId LEFT JOIN grade g ON g.id=tg.gradeId WHERE t.teacherId=?  GROUP BY t.teacherId";
		return super.querySingle(sql, new BeanHandler<TeacherInfoBean>(TeacherInfoBean.class), teacherId);
	}

	@Override
	public TeacherInfoBean findTeacherAddressIdById(Integer teacherId) {
		String sql="";
		return null;
	}

	@Override
	public int changeName(Integer teacherId, String teacherName) {
		String sql="UPDATE teacher SET teacherRealName=? WHERE teacherId=?";
		return super.update(sql, teacherName,teacherId);
	}
}
